package com.example.toutiao.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.toutiao.model.User;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    public DatabaseHelper(Context context) {
        super(context, "toutiao.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        // 创建商品表
        String createProductTable = "CREATE TABLE products (" +
                "id INTEGER PRIMARY KEY, " +
                "name TEXT, " +
                "price REAL, " +
                "description TEXT, " +
                "image TEXT)";
        db.execSQL(createProductTable);

        // 创建文章表，增加添加时间字段
        String createArticleTable = "CREATE TABLE articles (" +
                "id INTEGER PRIMARY KEY, " +
                "username TEXT, " +
                "title TEXT, " +
                "content TEXT, " +
                "time TEXT)";
        db.execSQL(createArticleTable);

        // 创建订单表
        String createOrderTable = "CREATE TABLE orders (" +
                "id INTEGER PRIMARY KEY, " +
                "username TEXT, " +
                "product_id INTEGER, " +
                "quantity INTEGER)";
        db.execSQL(createOrderTable);

        // 创建购物车表
        String createCartTable = "CREATE TABLE cart (" +
                "id INTEGER PRIMARY KEY, " +
                "username TEXT, " +
                "product_id INTEGER, " +
                "quantity INTEGER)";
        db.execSQL(createCartTable);

        // 创建用户表
        String createUserTable = "CREATE TABLE users (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "username TEXT UNIQUE, " +
                "password TEXT)";
        db.execSQL(createUserTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS products");
        db.execSQL("DROP TABLE IF EXISTS articles");
        db.execSQL("DROP TABLE IF EXISTS orders");
        db.execSQL("DROP TABLE IF EXISTS cart");
        onCreate(db);
    }

    // 查询商品信息
    public Product getProductById(int productId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Product product = null;
        Log.d("DatabaseHelper", "查询商品，商品ID: " + productId);

        Cursor cursor = db.query("products", null, "id = ?", new String[]{String.valueOf(productId)}, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            @SuppressLint("Range") String name = cursor.getString(cursor.getColumnIndex("name"));
            @SuppressLint("Range") double price = cursor.getDouble(cursor.getColumnIndex("price"));
            @SuppressLint("Range") String description = cursor.getString(cursor.getColumnIndex("description"));
            @SuppressLint("Range") String image = cursor.getString(cursor.getColumnIndex("image"));

            product = new Product(productId, name, price, description, image);

            cursor.close();
        } else {
            Log.d("DatabaseHelper", "未找到商品ID: " + productId);
        }
        db.close();
        return product;
    }

    public List<Article> getArticlesByUsername(String username) {
        List<Article> articles = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query("articles", null, "username=?", new String[]{username}, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex("title"));
                @SuppressLint("Range") String content = cursor.getString(cursor.getColumnIndex("content"));
                @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex("time"));
                @SuppressLint("Range") String author = cursor.getString(cursor.getColumnIndex("username"));
                articles.add(new Article(author,title, content, time));
            } while (cursor.moveToNext());
            cursor.close();
        }
        return articles;
    }

    // 根据用户名获取订单列表
    public List<Order> getOrdersByUsername(String username) {
        List<Order> orders = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String query = "SELECT id, username, product_id, quantity FROM orders WHERE username = ?";
        Cursor cursor = db.rawQuery(query, new String[]{username});

        if (cursor == null || !cursor.moveToFirst()) {
            Log.e("Database", "No data found for username: " + username);
            if (cursor != null) cursor.close();
            db.close();
            return orders;
        }

        String[] columnNames = cursor.getColumnNames();
        Log.d("Database", "Columns: " + Arrays.toString(columnNames));

        int idColumnIndex = cursor.getColumnIndex("id");
        int productIdColumnIndex = cursor.getColumnIndex("product_id");
        int quantityColumnIndex = cursor.getColumnIndex("quantity");

        if (idColumnIndex == -1 || productIdColumnIndex == -1 || quantityColumnIndex == -1) {
            Log.e("Database", "One or more columns are missing.");
            cursor.close();
            db.close();
            return orders;
        }

        do {
            int id = cursor.getInt(idColumnIndex);
            @SuppressLint("Range") String user = cursor.getString(cursor.getColumnIndex("username"));  // 获取用户名
            int productId = cursor.getInt(productIdColumnIndex);
            int quantity = cursor.getInt(quantityColumnIndex);
            orders.add(new Order(id, user, productId, quantity));

        } while (cursor.moveToNext());

        cursor.close();
        db.close();

        return orders;
    }


    public List<Product> getCartProducts(String username) {
        List<Product> cartProducts = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT product_id, quantity FROM cart WHERE username = ?", new String[]{username});

        if (cursor != null && cursor.moveToFirst()) {
            do {
                int productId = cursor.getInt(cursor.getColumnIndexOrThrow("product_id"));
                Product product = getProductById(productId);
                if (product != null) {
                    cartProducts.add(product);
                }
            } while (cursor.moveToNext());
            cursor.close();
        }

        db.close();
        return cartProducts;
    }


    public List<User> getAllUsers() {
        List<User> userList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query("users", null, null, null, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") String username = cursor.getString(cursor.getColumnIndex("username"));
                @SuppressLint("Range") String password = cursor.getString(cursor.getColumnIndex("password"));
                userList.add(new User(username, password));
            } while (cursor.moveToNext());
            cursor.close();
        }

        return userList;
    }

    public void deleteUser(String username) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("users", "username = ?", new String[]{username});
    }

    // 更新用户密码
    public void updatePassword(String username, String newPassword) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("password", newPassword);
        db.update("users", values, "username = ?", new String[]{username});
    }
    public void deleteAccount(String username) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("users", "username = ?", new String[]{username});
    }

    // 用户登录方法
    public boolean loginUser(String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query("users", null, "username=? AND password=?",
                new String[]{username, password}, null, null, null);

        boolean isLoginSuccessful = false;
        if (cursor != null && cursor.moveToFirst()) {
            isLoginSuccessful = true;
            cursor.close();
        }
        return isLoginSuccessful;
    }

    // 注册用户
    public boolean registerUser(String username, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query("users", null, "username = ?", new String[]{username}, null, null, null);

        if (cursor != null && cursor.getCount() > 0) {
            cursor.close();
            return false;
        }

        ContentValues values = new ContentValues();
        values.put("username", username);
        values.put("password", password);
        long result = db.insert("users", null, values);
        cursor.close();

        return result != -1;
    }

    public boolean addArticle(String username, String title, String content) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("username", username);
        values.put("title", title);
        values.put("content", content);

        // 添加当前时间戳
        String currentTime = String.valueOf(System.currentTimeMillis());
        values.put("time", currentTime);

        long result = db.insert("articles", null, values);
        db.close();
        return result != -1;
    }

    public boolean addProduct(String name, double price, String description) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("price", price);
        values.put("description", description);
        long productId = db.insert("products", null, values);
        db.close();
        return productId != -1;
    }


    public List<CartItem> getCartItemsByUsername(String username) {
        List<CartItem> cartItems = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        Log.d("查询用户名", "查询购物车商品，用户名：" + username);

        Cursor cursor = db.rawQuery("SELECT product_id, quantity FROM cart WHERE username = ?", new String[]{username});

        if (cursor != null && cursor.moveToFirst()) {

            do {
                int productId = cursor.getInt(cursor.getColumnIndexOrThrow("product_id"));
                int quantity = cursor.getInt(cursor.getColumnIndexOrThrow("quantity"));
                Product product = getProductById(productId);  // 根据商品ID获取商品信息

                if (product != null) {
                    cartItems.add(new CartItem(product, quantity));
                } else {
                    Log.d("查询结果", "未能找到商品ID: " + productId + " 的详细信息");
                }
                Log.d("购物车商品", "商品ID: " + productId + ", 数量: " + quantity);

            } while (cursor.moveToNext());
            cursor.close();
        } else {
            Log.d("查询结果", "没有查询到商品");
        }

        db.close();
        return cartItems;
    }


    public void deleteCartItemsByUsername(String username) {
        SQLiteDatabase db = this.getWritableDatabase();

        Log.d("删除用户名", "正在删除购物车商品，用户名：" + username);

        int rowsDeleted = db.delete("cart", "username = ?", new String[]{username});

        Log.d("删除结果", "删除了 " + rowsDeleted + " 条记录");

        db.close();
    }


    // 获取所有商品
    public List<Product> getAllProducts() {
        List<Product> productList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query("products", null, null, null, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));  // 获取 id
                @SuppressLint("Range") String name = cursor.getString(cursor.getColumnIndex("name"));
                @SuppressLint("Range") double price = cursor.getDouble(cursor.getColumnIndex("price"));
                @SuppressLint("Range") String description = cursor.getString(cursor.getColumnIndex("description"));
                @SuppressLint("Range") String image = cursor.getString(cursor.getColumnIndex("image"));

                // 使用 id 来创建 Product 对象
                productList.add(new Product(id, name, price, description, image));
            } while (cursor.moveToNext());
            cursor.close();
        }

        return productList;
    }


    public void addToCart(String username, int productId, int quantity) {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query("cart", null, "username=? AND product_id=?",
                new String[]{username, String.valueOf(productId)}, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            @SuppressLint("Range") int currentQty = cursor.getInt(cursor.getColumnIndex("quantity"));
            ContentValues values = new ContentValues();
            values.put("quantity", currentQty + quantity);
            db.update("cart", values, "username=? AND product_id=?", new String[]{username, String.valueOf(productId)});
            cursor.close();
        } else {
            ContentValues values = new ContentValues();
            values.put("username", username);
            values.put("product_id", productId);
            values.put("quantity", quantity);
            db.insert("cart", null, values);
        }
    }


    public boolean isProductInCart(String username, int productId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM cart WHERE username = ? AND product_id = ?", new String[]{username, String.valueOf(productId)});

        boolean isInCart = false;
        if (cursor != null && cursor.moveToFirst()) {
            int count = cursor.getInt(0);
            isInCart = count > 0;
            cursor.close();
        }
        db.close();
        return isInCart;
    }

    public void updateCartQuantity(String username, int productId, int quantityToAdd) {
        SQLiteDatabase db = this.getWritableDatabase();

        // 查询当前商品的数量
        Cursor cursor = db.rawQuery("SELECT quantity FROM cart WHERE username = ? AND product_id = ?", new String[]{username, String.valueOf(productId)});
        if (cursor != null && cursor.moveToFirst()) {
            @SuppressLint("Range") int currentQuantity = cursor.getInt(cursor.getColumnIndex("quantity"));
            int newQuantity = currentQuantity + quantityToAdd;
            ContentValues values = new ContentValues();
            values.put("quantity", newQuantity);
            db.update("cart", values, "username = ? AND product_id = ?", new String[]{username, String.valueOf(productId)});
            cursor.close();
        } else {
            addToCart(username, productId, quantityToAdd);
        }
        db.close();
    }

    // 将商品加入订单表
    public void addOrder(String username, int productId, int quantity) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("username", username);
        values.put("product_id", productId);
        values.put("quantity", quantity);
        db.insert("orders", null, values);
        db.close();
    }

    public void clearCart(String username) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete("cart", "username = ?", new String[]{username});
        db.close();
    }

}
